<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>SQL文件分析工具</title>
    <!-- 请勿在项目正式环境中引用该 layui.css 地址 -->
    <link rel="stylesheet" href="{{ url_for('dcs.static', filename='layuiadmin/layui/css/layui.css') }}">
    <link rel="stylesheet" href="{{ url_for('dcs.static', filename='layuiadmin/style/admin.css') }}">

    <style type="text/css">
        .layui-table-cell {
            height: auto;
            white-space: normal;
        }

        {# layui table 单元格 添加底色 #}
        .check-ok {
            background-color: green;
            color: white;
            padding: 5px;
        }

        .check-not-ok {
            background-color: red;
            color: white;
            padding: 5px;
        }
    </style>

</head>
<body>

<div class="layui-fluid">
    <div class="layui-row layui-col-space15">
        <div class="layui-col-md12">
            <div class="layui-card">
                <div class="layui-card-header">
                    <h2>SQL文件分析工具</h2>
                </div>
                <div class="layui-card-body">
                    {#<h4>普通卡片</h4>#}
                    <div class="layui-tabs layui-tabs-card" lay-options="{index: 0}">
                        <ul class="layui-tabs-header">
                            <li><h3>文件上传</h3></li>
                            <li><h3>分析结果查询</h3></li>
                        </ul>
                        <div class="layui-tabs-body">
                            <div class="layui-tabs-item">
                                {#内容-1#}
                                <div class="layui-upload">
                                    <button type="button" class="layui-btn layui-btn-normal" id="test-upload-testList">
                                        选择多文件
                                    </button>
                                    <div class="layui-upload-list">
                                        <table class="layui-table">
                                            <thead>
                                            <tr>
                                                <th>文件名</th>
                                                <th>大小</th>
                                                <th>状态</th>
                                                <th>操作</th>
                                            </tr>
                                            </thead>
                                            <tbody id="test-upload-demoList"></tbody>
                                        </table>
                                    </div>
                                    <button type="button" class="layui-btn" id="test-upload-testListAction">开始上传
                                    </button>
                                    <button type="button" class="layui-btn layui-btn-warm" id="fileAnalysisBtn">文件分析
                                    </button>
                                </div>
                                <div class="layui-row" style="margin: 10px 0;">
                                    <button class="layui-btn layui-btn-danger" id="btn-batch-delete">
                                        <i class="layui-icon">&#xe640;</i> 批量删除
                                    </button>
                                </div>
                                <table id="sfatTable" lay-filter="sfatTable"></table>
                            </div>
                            <div class="layui-tabs-item">
                                {#内容-2#}
                                <form class="layui-form">
                                    <div class="layui-form-item">
                                        <div class="layui-inline">
                                            <label class="layui-form-label">ID</label>
                                            <div class="layui-input-inline" style="width: 90px;">
                                                <input type="text" id="id" name="id" placeholder="ID"
                                                       class="layui-input"
                                                       lay-affix="clear">
                                            </div>
                                        </div>
                                        <div class="layui-inline">
                                            <label class="layui-form-label">文件名</label>
                                            <div class="layui-input-inline">
                                                <input type="text" id="file_name" name="file_name" placeholder="文件名"
                                                       class="layui-input"
                                                       lay-affix="clear">
                                            </div>
                                        </div>
                                        <div class="layui-inline">
                                            <label class="layui-form-label">文件内容</label>
                                            <div class="layui-input-inline">
                                                <input type="text" id="file_content" name="file_content"
                                                       placeholder="文件内容"
                                                       class="layui-input"
                                                       lay-affix="clear">
                                            </div>
                                        </div>
                                        <div class="layui-inline">
                                            <label class="layui-form-label">SQL序号</label>
                                            <div class="layui-input-inline" style="width: 100px;">
                                                <input type="text" id="sql_num" name="sql_num"
                                                       placeholder="SQL序号"
                                                       class="layui-input"
                                                       lay-affix="clear">
                                            </div>
                                        </div>
                                        <div class="layui-inline">
                                            <label class="layui-form-label">SQL类型</label>
                                            <div class="layui-input-inline">
                                                <input type="text" id="sql_type" name="sql_type"
                                                       placeholder="SQL类型"
                                                       class="layui-input"
                                                       lay-affix="clear">
                                            </div>
                                        </div>
                                        <div class="layui-inline">
                                            <label class="layui-form-label">SQL内容</label>
                                            <div class="layui-input-inline">
                                                <input type="text" id="sql_detail" name="sql_detail"
                                                       placeholder="SQL内容"
                                                       class="layui-input"
                                                       lay-affix="clear">
                                            </div>
                                        </div>
                                        <div class="layui-inline">
                                            <label class="layui-form-label">表名</label>
                                            <div class="layui-input-inline">
                                                <input type="text" id="table_name" name="table_name"
                                                       placeholder="表名"
                                                       class="layui-input"
                                                       lay-affix="clear">
                                            </div>
                                        </div>
                                        <div class="layui-inline">
                                            <label class="layui-form-label">索引名</label>
                                            <div class="layui-input-inline">
                                                <input type="text" id="index_name" name="index_name"
                                                       placeholder="索引名"
                                                       class="layui-input"
                                                       lay-affix="clear">
                                            </div>
                                        </div>

                                        <div class="layui-inline">
                                            <label class="layui-form-label"
                                                   style="width: 180px;">
                                                是否更新last_update_date
                                            </label>
                                            <div class="layui-input-inline" style="width: 100px;">
                                                <select id="has_last_update_date" name="has_last_update_date">
                                                    <option value=""></option>
                                                    <option value="Y">Y</option>
                                                    <option value="N">N</option>
                                                </select>
                                            </div>
                                        </div>
                                        <div class="layui-inline">
                                            <label class="layui-form-label"
                                                   style="width: 180px;">
                                                update是否有where条件
                                            </label>
                                            <div class="layui-input-inline" style="width: 160px;">
                                                <select id="update_has_where_condition"
                                                        name="update_has_where_condition">
                                                    <option value=""></option>
                                                    <option value="Y">Y</option>
                                                    <option value="N">N</option>
                                                    <option value="update语法不正确">update语法不正确</option>
                                                </select>
                                            </div>
                                        </div>

                                        <div class="layui-inline">
                                            <label class="layui-form-label"
                                                   style="width: 180px;">
                                                delete是否有where条件
                                            </label>
                                            <div class="layui-input-inline" style="width: 100px;">
                                                <select id="delete_has_where_condition"
                                                        name="delete_has_where_condition">
                                                    <option value=""></option>
                                                    <option value="Y">Y</option>
                                                    <option value="N">N</option>
                                                </select>
                                            </div>
                                        </div>
                                        <div class="layui-inline">
                                            <label class="layui-form-label">
                                                无中文分号
                                            </label>
                                            <div class="layui-input-inline" style="width: 100px;">
                                                <select id="check_chinese_semicolon"
                                                        name="check_chinese_semicolon">
                                                    <option value=""></option>
                                                    <option value="Y">Y</option>
                                                    <option value="N">N</option>
                                                </select>
                                            </div>
                                        </div>

                                        <div class="layui-inline">
                                            <label class="layui-form-label" style="width: 180px;">
                                                有search_path
                                            </label>
                                            <div class="layui-input-inline" style="width: 100px;">
                                                <select id="first_5_lines_has_search_path"
                                                        name="first_5_lines_has_search_path">
                                                    <option value=""></option>
                                                    <option value="Y">Y</option>
                                                    <option value="N">N</option>
                                                </select>
                                            </div>
                                        </div>

                                        {#</div> <div class="form-footer">#}
                                        <button class="layui-btn layuiadmin-btn-list" lay-submit lay-filter="formSubmit"
                                                id="btnSearch">
                                            <i class="layui-icon layui-icon-search layuiadmin-button-btn"></i>搜索
                                        </button>

                                        <button class="layui-btn layuiadmin-btn-list layui-btn-primary" type="reset"
                                                id="customReset">
                                            <i class="layui-icon layui-icon-set-sm layuiadmin-button-btn"></i>重置
                                        </button>

                                        <button class="layui-btn layuiadmin-btn-list layui-btn-normal" id="exportAllBtn"
                                                lay-submit lay-filter="export">
                                            <i class="layui-icon layui-icon-export layuiadmin-button-btn"></i>导出cvs
                                        </button>
                                    </div>
                                </form>

                                <table id="analysisResultTable" lay-filter="analysisResultTable"></table>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- 请勿在项目正式环境中引用该 layui.js 地址 -->
<script src="{{ url_for('dcs.static', filename='layuiadmin/layui/layui.js') }}"></script>

<script>
    layui.config({
        // {#base: '../../../layuiadmin/' //静态资源所在路径#}
        base: "{{ url_for('dcs.static', filename='layuiadmin/') }}" //静态资源所在路径
    }).extend({
        index: 'lib/index' //主入口模块
    }).use(['index', 'upload', 'table', 'layer', 'form'], function () {
        var $ = layui.jquery;
        var upload = layui.upload;
        var table = layui.table;
        var layer = layui.layer;
        var form = layui.form;

        // 初始化表格
        var sfatTableIns = table.render({
            elem: '#sfatTable',
            url: '/sfat/api/uploadedFiles',
            method: 'get',
            page: true,
            // height: 600,
            cols: [[
                {type: 'checkbox'},  // 复选框列
                {field: 'id', title: 'ID', width: 80, align: 'center'},
                {
                    field: 'filename',
                    title: '文件名',
                    width: 450,
                    align: 'left',
                    templet: addlink,
                    sort: true
                },
                {field: 'upload_time', title: '上传时间', width: 200, align: 'left'},
                {field: 'file_size', title: '文件大小(b)', width: 200, align: 'left'},
                {fixed: 'right', title: '操作', width: 134, minWidth: 150, toolbar: '#operateTpl'}
            ]]
        });

        function addlink(d) {
            var html = '<div><a rel="nofollow" style="color:#1E9FFF" href="javascript:void(0);" lay-event="infoDetail">'
                + d.filename + '</a></div>';
            return html
        }

        //多文件列表示例
        var demoListView = $('#test-upload-demoList')
            , uploadListIns = upload.render({
            elem: '#test-upload-testList'
            , url: '/sfat/upload'
            , accept: 'file'
            , multiple: true
            , auto: false
            , bindAction: '#test-upload-testListAction'
            , choose: function (obj) {
                var files = this.files = obj.pushFile(); //将每次选择的文件追加到文件队列
                //读取本地文件
                obj.preview(function (index, file, result) {
                    var tr = $(['<tr id="upload-' + index + '">'
                        , '<td>' + file.name + '</td>'
                        , '<td>' + (file.size / 1014).toFixed(1) + 'kb</td>'
                        , '<td>等待上传</td>'
                        , '<td>'
                        , '<button class="layui-btn layui-btn-mini test-upload-demo-reload layui-hide">重传</button>'
                        , '<button class="layui-btn layui-btn-mini layui-btn-danger test-upload-demo-delete">删除</button>'
                        , '</td>'
                        , '</tr>'].join(''));

                    //单个重传
                    tr.find('.test-upload-demo-reload').on('click', function () {
                        obj.upload(index, file);
                    });

                    //删除
                    tr.find('.test-upload-demo-delete').on('click', function () {
                        delete files[index]; //删除对应的文件
                        tr.remove();
                        uploadListIns.config.elem.next()[0].value = ''; //清空 input file 值，以免删除后出现同名文件不可选
                    });

                    demoListView.append(tr);
                });
            }
            , done: function (res, index, upload) {
                if (res.code == 0) { //上传成功
                    var tr = demoListView.find('tr#upload-' + index)
                        , tds = tr.children();
                    tds.eq(2).html('<span style="color: #5FB878;">上传成功</span>');
                    tds.eq(3).html(''); //清空操作
                    // 表格重新加载
                    sfatTableIns.reload({
                        {#where: data.field,#}
                        page: {curr: 1}
                    });
                    return delete this.files[index]; //删除文件队列已经上传成功的文件
                }
                this.error(index, upload);
            }
            , error: function (index, upload) {
                var tr = demoListView.find('tr#upload-' + index)
                    , tds = tr.children();
                tds.eq(2).html('<span style="color: #FF5722;">上传失败</span>');
                tds.eq(3).find('.test-upload-demo-reload').removeClass('layui-hide'); //显示重传
            }
        });

        // 查看数据详情
        function detailShowForm(data) {
            var title = '查看详情';
            {#var index = layer.open({
                type: 1,
                title: title,
                content: $('#formDialog').html(),
                // area: ['550px', '650px'],
                area: '700px',
                success: function () {

                }
            });#}

            var index = layer.open({
                type: 2,
                offset: 'auto',
                title: '查看详情 - ' + data.filename,
                skin: 'layui-layer-rim', //加上边框
                shadeClose: true,
                chade: false,
                maxmin: true,
                area: ['60%', '70%'], //宽高
                content: '/sfat/codeView?id=' + data.id
            });

            // 关闭窗口按钮
            $('#closeWindowsBtn').click(function () {
                layer.close(index); // 关闭加载层
            });
        }

        table.on('tool(sfatTable)', function (obj) {
            var data = obj.data;
            if (obj.event === 'infoDetail') {
                detailShowForm(data);
            }
        });

        // 批量删除按钮事件
        $('#btn-batch-delete').click(function () {
            var checkStatus = table.checkStatus('sfatTable');
            var selectedIds = checkStatus.data.map(item => item.id);

            if (selectedIds.length === 0) {
                layer.msg('请至少选择一条记录');
                return;
            }

            layer.confirm('确定要删除选中的 ' + selectedIds.length + ' 条记录吗？', {
                icon: 3,
                title: '删除确认'
            }, function (index) {
                // 发送AJAX请求
                $.ajax({
                    url: '/sfat/api/uploadedFilesBatchDelete',
                    type: 'POST',
                    contentType: 'application/json',
                    data: JSON.stringify({ids: selectedIds}),
                    success: function (res) {
                        if (res.success) {
                            layer.msg('成功删除 ' + res.deleted_count + ' 条记录', {icon: 1});
                            table.reload('sfatTable'); // 刷新表格
                        } else {
                            layer.msg('删除失败: ' + res.message, {icon: 2});
                        }
                    },
                    error: function () {
                        layer.msg('请求失败', {icon: 2});
                    }
                });
                layer.close(index);
            });
        });

        // 初始化表格
        var analysisResultTableIns = table.render({
            elem: '#analysisResultTable',
            url: '/sfat/api/getSqlFileAnalysisResults',
            method: 'get',
            page: true,
            // height: 600,
            cols: [[
                {field: 'id', title: 'ID', width: 80, align: 'center'},
                {
                    field: 'file_name',
                    title: '文件名',
                    width: 250,
                    align: 'left',
                    // fixed: 'left',
                    templet: analysisAddlink1,
                    sort: true
                },
                {field: 'sql_num', title: 'SQL序号', width: 90, align: 'center'},
                {field: 'sql_type', title: 'SQL类型', width: 120, align: 'left'},
                {field: 'line_numbers', title: '行号', width: 80, align: 'center'},
                {field: 'line_count', title: 'SQL行数', width: 90, align: 'center'},
                {
                    {#field: 'each_sql_content',#}
                    field: 'sql_detail',
                    title: 'SQL内容',
                    templet: analysisAddlink2,
                    width: 200,
                    align: 'left'
                },

                {field: 'table_name', title: '表名', width: 200, align: 'left'},
                {field: 'index_name', title: '索引名', width: 200, align: 'left'},
                {field: 'has_last_update_date', title: '更新last_update_date', width: 100, align: 'center'},
                {field: 'update_has_where_condition', title: 'update有where条件', width: 100, align: 'center'},
                {field: 'delete_has_where_condition', title: 'delete有where条件', width: 100, align: 'center'},
                {field: 'check_chinese_semicolon', title: '无中文分号', width: 100, align: 'center'},
                {field: 'first_5_lines_has_search_path', title: '有search_path', width: 100, align: 'center'}
                //,{fixed: 'right', title: '操作', width: 134, minWidth: 150, toolbar: '#operateTpl'}
            ]],
            done: function (res) {
                // layui table 单元格 添加底色。遍历表格中的每一行
                $('.layui-table tbody tr').each(function () {
                    // 获取当前行的所有单元格
                    var $cells = $(this).children('td');
                    // 遍历每一个单元格
                    $cells.each(function () {
                        var $cell = $(this);
                        var value = $cell.text();
                        // 假设我们想为值大于10的单元格设置背景色
                        if (value === 'Y') {
                            // $cell.css('background-color', 'green');
                            $cell.addClass('check-ok');
                        } else if (value === 'N') {
                            // $cell.css('background-color', 'red');
                            $cell.addClass('check-not-ok');
                        }
                    });
                });
            }
        });

        // 绑定表单提交事件。点击搜索按钮，提交表单
        form.on('submit(formSubmit)', function (data) {
            analysisResultTableIns.reload({
                where: data.field,
                page: {curr: 1}
            });
            return false;
        });

        // 导出按钮提交表单
        form.on('submit(export)', function (data) {
            exportAllDataFromConditions();
            return false;
        });

        function exportAllDataFromConditions() {
            layer.confirm('导出当前查询条件的所有数据吗？', function (index) {
                layer.close(index);
                // 显示加载中
                var loadIndex3 = layer.load(2);

                var id2 = $('#id').val();
                var file_name2 = $('#file_name').val();
                var file_content2 = $('#file_content').val();
                var sql_num2 = $('#sql_num').val();
                var sql_type2 = $('#sql_type').val();
                var sql_detail2 = $('#sql_detail').val();
                var table_name2 = $('#table_name').val();
                var index_name2 = $('#index_name').val();
                var has_last_update_date2 = $('#has_last_update_date').val();
                var update_has_where_condition2 = $('#update_has_where_condition').val();
                var delete_has_where_condition2 = $('#delete_has_where_condition').val();
                var check_chinese_semicolon2 = $('#check_chinese_semicolon').val();
                var first_5_lines_has_search_path2 = $('#first_5_lines_has_search_path').val();

                var exportUrl = '/sfat/api/getSqlFileAnalysisResults?export=1&';

                exportUrl += 'id' + '=' + encodeURIComponent(id2) + '&';
                exportUrl += 'file_name' + '=' + encodeURIComponent(file_name2) + '&';
                exportUrl += 'file_content' + '=' + encodeURIComponent(file_content2) + '&';
                exportUrl += 'sql_num' + '=' + encodeURIComponent(sql_num2) + '&';
                exportUrl += 'sql_type' + '=' + encodeURIComponent(sql_type2) + '&';
                exportUrl += 'sql_detail' + '=' + encodeURIComponent(sql_detail2) + '&';
                exportUrl += 'table_name' + '=' + encodeURIComponent(table_name2) + '&';
                exportUrl += 'index_name' + '=' + encodeURIComponent(index_name2) + '&';
                exportUrl += 'has_last_update_date' + '=' + encodeURIComponent(has_last_update_date2) + '&';
                exportUrl += 'update_has_where_condition' + '=' + encodeURIComponent(update_has_where_condition2) + '&';
                exportUrl += 'delete_has_where_condition' + '=' + encodeURIComponent(delete_has_where_condition2) + '&';
                exportUrl += 'check_chinese_semicolon' + '=' + encodeURIComponent(check_chinese_semicolon2) + '&';
                exportUrl += 'first_5_lines_has_search_path' + '=' + encodeURIComponent(first_5_lines_has_search_path2) + '&';

                // 创建隐藏的iframe进行下载
                var iframe = document.createElement('iframe');
                iframe.style.display = 'none';
                iframe.src = exportUrl;
                document.body.appendChild(iframe);

                layer.close(loadIndex3);
            });
        }

        function analysisAddlink1(d) {
            var html = '<div><a rel="nofollow" style="color:#1E9FFF" href="javascript:void(0);" ' +
                'lay-event="analysisAddlinkDetail1">'
                + d.file_name + '</a></div>';
            return html
        }

        function analysisAddlink2(d) {
            var html = '<div><a rel="nofollow" style="color:#1E9FFF" href="javascript:void(0);" ' +
                'lay-event="analysisAddlinkDetail2">'
                + (d.sql_detail.substring(0, 20) + '...') + '</a></div>';
            return html
        }

        table.on('tool(analysisResultTable)', function (obj) {
            var data = obj.data;
            if (obj.event === 'analysisAddlinkDetail1') {
                analysisDetailShowForm1(data);
            } else if (obj.event === 'analysisAddlinkDetail2') {
                analysisDetailShowForm2(data);
            }
        });

        // 查看数据详情
        function analysisDetailShowForm1(data) {
            var title = '查看详情';
            var index = layer.open({
                type: 2,
                offset: 'auto',
                title: '查看详情 - ' + data.file_name,
                skin: 'layui-layer-rim', //加上边框
                shadeClose: true,
                chade: false,
                maxmin: true,
                area: ['60%', '70%'], //宽高
                content: '/sfat/codeView?id=' + data.id
            });

            // 关闭窗口按钮
            $('#closeWindowsBtn').click(function () {
                layer.close(index); // 关闭加载层
            });
        }

        // 查看数据详情
        function analysisDetailShowForm2(data) {
            var title = '查看详情';
            var index = layer.open({
                type: 2,
                offset: 'auto',
                title: '查看详情 - ' + data.file_name + ' - SQL' + data.sql_num,
                skin: 'layui-layer-rim', //加上边框
                shadeClose: true,
                chade: false,
                maxmin: true,
                area: ['60%', '70%'], //宽高
                content: '/sfat/codeView2?id=' + data.id + '&sql_num=' + data.sql_num
            });

            // 关闭窗口按钮
            $('#closeWindowsBtn').click(function () {
                layer.close(index); // 关闭加载层
            });
        }

        // 分析文件按钮
        $('#fileAnalysisBtn').click(function () {
            doFileAnalysisJob();
        });

        function doFileAnalysisJob() {
            layer.confirm('确定对所有已上传成功的文件进行分析吗？', function (index) {
                layer.close(index);
                // 显示加载中
                // var loadIndex2 = layer.load(2);
                var loadIndex2 = layer.msg('后台文件分析任务进行中，请耐心等待...', {
                    icon: 16,
                    shade: 0.01,
                    time: 0
                });
                // 后台执行文件分析接口
                $.ajax({
                    url: '/sfat/api/initFileAnalysisData/',
                    type: 'GET',
                    dataType: 'json',
                    success: function (data) {

                        // alert(data);
                        // const returnDataJson = JSON.stringify(data);
                        // alert("returnDataJson = " + returnDataJson);
                        // alert("data.msg = " + data.msg);
                        if (data.code === 1) {
                            layer.alert('文件分析成功！', {
                                icon: 1,
                                shadeClose: true,
                                title: '提示'
                            });
                            layer.close(loadIndex2); // 关闭加载层
                        } else if (data.code === -1) {
                            layer.alert('文件分析失败！' + data.msg, {
                                icon: 2,
                                shadeClose: true,
                                title: '提示'
                            });
                            layer.close(loadIndex2);
                        } else {
                            layer.alert('未知错误！' + data.msg, {
                                icon: 2,
                                shadeClose: true,
                                title: '提示'
                            });
                            layer.close(loadIndex2);
                        }
                    },
                    error: function (xhr, status, error) {
                        layer.close(loadIndex2);
                        // 基础错误信息
                        var errorMsg = "请求失败: " + status + "<br>错误码: " + xhr.status;

                        // 尝试解析服务器返回的详细错误信息
                        try {
                            var response = JSON.parse(xhr.responseText);
                            alert("response = " + response)
                            if (response.message) errorMsg += "<br>消息: " + response.message;
                            if (response.error) errorMsg += "<br>错误详情: " + response.error;
                            if (response.msg) errorMsg += "<br>错误详情: " + response.msg;
                        } catch (e) {
                            errorMsg += "<br>响应原始数据: " + xhr.responseText;
                        }

                        // 控制台打印完整错误对象
                        console.error("完整错误对象:", xhr);

                        // 弹窗显示详细错误（使用 Layui 的 layer）
                        layer.open({
                            type: 1,
                            title: '请求错误详情',
                            area: ['500px', '300px'],
                            content: '<div class="layui-padding">' + errorMsg + '</div>'
                        });
                    }
                });
                table.reload('analysisResultTable'); // 刷新表格
                // layer.close(loadIndex2);
            });
        }

    });
</script>

</body>
</html>